Programming Microsoft Visual Basic .NET/Programming Microsoft Visual Basic .NET (Microsoft Press)(X08-78517)(2002).bin/setup/vbnet/21 adonet disconnected/adonetdisconnected/dataadapter.vb

Text File | 2002-03-16 | 18.4 KB | 457 lines

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class DataAdapter
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents btnReadData As System.Windows.Forms.Button
    Friend WithEvents btnTableMappings As System.Windows.Forms.Button
    Friend WithEvents btnFillSchema As System.Windows.Forms.Button
    Friend WithEvents btnReadMultiple As System.Windows.Forms.Button
    Friend WithEvents btnUpdate As System.Windows.Forms.Button
    Friend WithEvents btnCustomCommands As System.Windows.Forms.Button

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.Container

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid()
        Me.btnReadData = New System.Windows.Forms.Button()
        Me.btnReadMultiple = New System.Windows.Forms.Button()
        Me.btnTableMappings = New System.Windows.Forms.Button()
        Me.btnUpdate = New System.Windows.Forms.Button()
        Me.btnFillSchema = New System.Windows.Forms.Button()
        Me.btnCustomCommands = New System.Windows.Forms.Button()
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.Anchor = (((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right)
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(120, 16)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(544, 280)
        Me.DataGrid1.TabIndex = 0
        '
        'btnReadData
        '
        Me.btnReadData.Location = New System.Drawing.Point(8, 16)
        Me.btnReadData.Name = "btnReadData"
        Me.btnReadData.Size = New System.Drawing.Size(104, 40)
        Me.btnReadData.TabIndex = 1
        Me.btnReadData.Text = "Read data"
        '
        'btnReadMultiple
        '
        Me.btnReadMultiple.Location = New System.Drawing.Point(8, 64)
        Me.btnReadMultiple.Name = "btnReadMultiple"
        Me.btnReadMultiple.Size = New System.Drawing.Size(104, 40)
        Me.btnReadMultiple.TabIndex = 1
        Me.btnReadMultiple.Text = "Read three tables"
        '
        'btnTableMappings
        '
        Me.btnTableMappings.Location = New System.Drawing.Point(8, 112)
        Me.btnTableMappings.Name = "btnTableMappings"
        Me.btnTableMappings.Size = New System.Drawing.Size(104, 40)
        Me.btnTableMappings.TabIndex = 1
        Me.btnTableMappings.Text = "Table Mappings"
        '
        'btnUpdate
        '
        Me.btnUpdate.Location = New System.Drawing.Point(8, 208)
        Me.btnUpdate.Name = "btnUpdate"
        Me.btnUpdate.Size = New System.Drawing.Size(104, 40)
        Me.btnUpdate.TabIndex = 1
        Me.btnUpdate.Text = "Update data"
        '
        'btnFillSchema
        '
        Me.btnFillSchema.Location = New System.Drawing.Point(8, 160)
        Me.btnFillSchema.Name = "btnFillSchema"
        Me.btnFillSchema.Size = New System.Drawing.Size(104, 40)
        Me.btnFillSchema.TabIndex = 1
        Me.btnFillSchema.Text = "FillSchema method"
        '
        'btnCustomCommands
        '
        Me.btnCustomCommands.Location = New System.Drawing.Point(8, 256)
        Me.btnCustomCommands.Name = "btnCustomCommands"
        Me.btnCustomCommands.Size = New System.Drawing.Size(104, 40)
        Me.btnCustomCommands.TabIndex = 1
        Me.btnCustomCommands.Text = "Custom commands"
        '
        'DataAdapter
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(7, 17)
        Me.ClientSize = New System.Drawing.Size(672, 309)
        Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.btnCustomCommands, Me.btnUpdate, Me.btnReadMultiple, Me.btnFillSchema, Me.btnTableMappings, Me.btnReadData, Me.DataGrid1})
        Me.Font = New System.Drawing.Font("Microsoft Sans Serif", 11.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Name = "DataAdapter"
        Me.Text = "DataAdapter"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    ' ensure that only one of these constants is -1
#Const OLEDBBIBLIO = -1
#Const OLEDBPUBS = 0
#Const SQLPUBS = 0

    Dim ds As New DataSet()

    Private Sub btnReadData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadData.Click
        ' Create a DataAdapter that reads and writes the Publishers table.
        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBBIBLIO Then
            Dim cn As New OleDbConnection(BiblioConnString)
            dim da as New OleDbDataAdapter(sql, cn)
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf SQLPUBS Then
            Dim cn As New SqlConnection(SqlPubsConnString)
            dim da as New SqlDataAdapter (sql, cn)
#End If

#Const DA_BASED_ON_PARAMETERIZED_COMMAND = False

#If DA_BASED_ON_PARAMETERIZED_COMMAND Then
            ' Create a Command object with parameters
            Dim sql As String = "SELECT * FROM Publishers WHERE Name LIKE ?"
            Dim cmd As New OleDbCommand(sql, cn)
            ' Create the parameter with an initial value.
            cmd.Parameters.Add("PubNameLike", "S%")
            ' Create the DataAdapter based on the parameterized command.
            da = New DataAdapter(cmd)
            ' Fill the DataSet with a subset of the Publishers table.
            da.Fill(ds, "Publishers")

            cmd.Parameters(0).Value = "M%"
#End If

        ' fill the dataset
        da.Fill(ds, "Publishers")
        ' show the result.
        DataGrid1.DataSource = ds.Tables("Publishers")
    End Sub

    ' demonstrate how a data adapter can read 3 tables

    Private Sub btnReadMultiple_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadMultiple.Click
        ' Create a DataAdapter that reads and writes 3 tables.
        Dim sql As String = "SELECT * FROM Publishers;SELECT * FROM Titles;SELECT * FROM Authors"
#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf SQLPUBS Then
            Dim cn As New SqlConnection(SqlPubsConnString)
            Dim da As New SqlDataAdapter(sql, cn)
#End If

        ' Create and Fill the DataSet's Publishers table.
        da.Fill(ds, "Publishers")

        ' change the names of 2nd and 3rd table
        ds.Tables(1).TableName = "Titles"
        ds.Tables(2).TableName = "Authors"
        DataGrid1.DataSource = ds.Tables("Titles")
    End Sub

    ' demonstrate the TableMappings object

    Private Sub btnTableMappings_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTableMappings.Click

        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf SQLPUBS Then
            Dim cn As New SqlConnection(SqlPubsConnString)
            Dim da As New SqlDataAdapter(sql, cn)
#End If

        ' Adds an element to the TableMappings collection.
        ' (Returns a DataTableMapping object.)
        With da.TableMappings.Add("Publishers", "DataPublishers")
            ' Add two element to the ColumnMappings collection.
            .ColumnMappings.Add("pub_id", "ID")
            .ColumnMappings.Add("pub_name", "Name")
        End With

        ' The following block of code makes the DataAdapter import only two fields.
#Const IMPORT_ONLY_TWO_FIELDS = False

#If IMPORT_ONLY_TWO_FIELDS Then
                With ds.Tables.Add("DataPublishers")
                    .Columns.Add("ID", GetType(String))
                    .Columns.Add("Name", GetType(String))
                End With
                da.MissingSchemaAction = MissingSchemaAction.Ignore
#End If

        ' Fill the DataSet with the source Publishers table, using the prepared mappings
        da.Fill(ds, "Publishers")
        DataGrid1.DataSource = ds.Tables("DataPublishers")
    End Sub

    ' demonstrate the FillSchema method

    Private Sub btnFillSchema_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFillSchema.Click
        Dim sql As String = "SELECT * FROM Publishers;SELECT * FROM Titles;SELECT * FROM authors"

#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf SQLPUBS Then
            Dim cn As New SqlConnection(SqlPubsConnString)
            Dim da As New SqlDataAdapter(sql, cn)
#End If

        With da.TableMappings.Add("Publishers", "DataPublishers")
            ' Add two element to the ColumnMappings collection.
            .ColumnMappings.Add("pub_id", "ID")
            .ColumnMappings.Add("pub_name", "Name")
        End With
        da.FillSchema(ds, SchemaType.Source, "Publishers")
        ' Change the name of the 2nd and 3rd tables.
        ds.Tables(1).TableName = "Titles"
        ds.Tables(2).TableName = "Authors"

        DataGrid1.DataSource = ds.Tables("Authors")
    End Sub

    ' perform an update

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim sql As String = "SELECT * FROM Publishers"

#If OLEDBBIBLIO Then
        Dim cn As New OleDbConnection(BiblioConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf SQLPUBS Then
            Dim cn As New SqlConnection(SqlPubsConnString)
            Dim da As New SqlDataAdapter(sql, cn)
#End If
        cn.Open()
        da.Fill(ds, "Publishers")

        DataGrid1.DataSource = ds.Tables("Publishers")

#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cmdBuilder As New OleDbCommandBuilder(da)
        cmdBuilder.QuotePrefix = "["
        cmdBuilder.QuoteSuffix = "]"
        Dim par As OleDbParameter

#ElseIf SQLPUBS Then
            Dim cmdBuilder As New SqlCommandBuilder(da)
            Dim par As SqlParameter
#End If
        ' generate the three default commands
        da.DeleteCommand = cmdBuilder.GetDeleteCommand
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand

        ' Display info on auto-generated commands
        Debug.WriteLine(da.InsertCommand.CommandText)
        Debug.WriteLine(da.UpdateCommand.CommandText)
        Debug.WriteLine(da.DeleteCommand.CommandText)
        For Each par In da.UpdateCommand.Parameters
            Debug.WriteLine(par.ParameterName & " => " & par.SourceColumn & "(" & par.SourceVersion.ToString & ")")
        Next

#If OLEDBBIBLIO Then
        With ds.Tables("Publishers")
            ' Modify the first record
            .Rows(0)("Name") = .Rows(0)("Name").ToString & " ***"

            ' Add a new record
            Dim dr As DataRow = .NewRow
            dr("Name") = "VB2TheMax"
            .Rows.Add(dr)
        End With
#Else
        With ds.Tables("Publishers")
            ' Modify the first record
            .Rows(0)("pub_name") = .Rows(0)("pub_name").ToString & " ***"
            ' Add a new record
            Dim dr As DataRow = .NewRow
            dr("pub_id") = "9988"
            dr("pub_name") = "Vb2TheMax"
            dr("city") = "Bari"
            dr("country") = "Italy"
            .Rows.Add(dr)
        End With
#End If

        ' Send changes them to the database.
        da.Update(ds, "Publishers")
    End Sub

    ' demonstrates using custom update commands

    Private Sub btnCustomCommands_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCustomCommands.Click
        ' This requires that you've added a field named LastUpdate of type timestamp
        ' to the Authors table.
        Dim sql As String = "SELECT au_id,au_fname,au_lname,lastupdate FROM Authors"

        ' This sample only works for SQL server
#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cn As New OleDbConnection(OledbPubsConnString)
        Dim da As New OleDbDataAdapter(sql, cn)
#ElseIf SQLPUBS Then
            Dim cn As New SqlConnection(SqlPubsConnString)
            Dim da As New SqlDataAdapter(sql, cn)
#End If
        ' No need to explicitly open and close the connection.
        'cn.Open()

        da.Fill(ds, "Authors")
        DataGrid1.DataSource = ds.Tables("Authors")

        ' Generate custom commands
#If OLEDBBIBLIO Or OLEDBPUBS Then
        Dim cmdBuilder As New OleDbCommandBuilder(da)
#Else
            Dim cmdBuilder As New SqlCommandBuilder(da)
#End If
        da.InsertCommand = cmdBuilder.GetInsertCommand
        da.UpdateCommand = cmdBuilder.GetUpdateCommand
        da.DeleteCommand = cmdBuilder.GetDeleteCommand

#Const GENERATE_CUSTOM_DELETE_COMMAND = False
#Const GENERATE_CUSTOM_DELETE_WITH_TIMESTAMP = False


#If GENERATE_CUSTOM_DELETE_COMMAND Then
            ' create a custom delete command
            Dim cmdDelete As New OleDbCommand("DELETE FROM Authors WHERE Au_ID = ?", cn)
            With cmdDelete.Parameters.Add("@p1", GetType(Integer))
                .SourceColumn = "Au_id"
                .SourceVersion = DataRowVersion.Original
            End With
            da.DeleteCommand = cmdDelete

            ' Create a custom update command.
            Dim cmdUpdate As New OleDbCommand("UPDATE Authors SET Author = ? , [Year Born] = ? WHERE Au_ID = ?", cn)
            ' Add arguments for the SET clause, that use the current field value.
            With cmdUpdate.Parameters.Add("@p1", GetType(String))
                .SourceColumn = "Authors"
                .SourceVersion = DataRowVersion.Current
            End With
            With cmdUpdate.Parameters.Add("@p2", GetType(Integer))
                .SourceColumn = "Year Born"
                .SourceVersion = DataRowVersion.Current
            End With
            ' Add the only argument in the WHERE clase, that uses the original field value.
            With cmdUpdate.Parameters.Add("@p3", GetType(Integer))
                .SourceColumn = "Au_id"
                .SourceVersion = DataRowVersion.Original
            End With
            da.UpdateCommand = cmdUpdate

#ElseIf GENERATE_CUSTOM_DELETE_WITH_TIMESTAMP Then
            ' Create a custom delete command that uses the timestamp field.
        ' IMPORTANT: this assumes that you've expanded the Authors table
        ' with a LastUpdate field of type TimeStamp

            Dim cmdDelete As New OleDbCommand("DELETE FROM Authors WHERE Au_ID = ? And LastUpdate=?", cn)
            With cmdDelete.Parameters.Add("@p1", GetType(Integer))
                .SourceColumn = "Au_id"
                .SourceVersion = DataRowVersion.Original
            End With
            ' Timestamp values are saved as arrays of Byte.
            With cmdDelete.Parameters.Add("@p2", GetType(Byte()))
                .SourceColumn = "LastUpdate"
                .SourceVersion = DataRowVersion.Original
            End With
            da.DeleteCommand = cmdDelete

            ' Create a custom update command.
            Dim cmdUpdate As New OleDbCommand("UPDATE Authors SET au_fname = ? , au_lname = ? WHERE au_id = ? AND LastUpdate=?", cn)
            ' Add arguments for the SET clause, that use the current field value.
            With cmdUpdate.Parameters.Add("@p1", GetType(String))
                .SourceColumn = "au_fname"
                .SourceVersion = DataRowVersion.Current
            End With
            With cmdUpdate.Parameters.Add("@p2", GetType(Integer))
                .SourceColumn = "au_lname"
                .SourceVersion = DataRowVersion.Current
            End With
            ' Add the arguments in the WHERE clase, that uses the original field value.
            With cmdUpdate.Parameters.Add("@p3", GetType(Integer))
                .SourceColumn = "Au_id"
                .SourceVersion = DataRowVersion.Original
            End With
            ' Timestamp values are saved as arrays of Byte.
            With cmdUpdate.Parameters.Add("@p4", GetType(Byte()))
                .SourceColumn = "LastUpdate"
                .SourceVersion = DataRowVersion.Original
            End With
            da.UpdateCommand = cmdUpdate
#End If

        ' delete some rows
        ' NOTE: these statement are commented because they would
        ' raise a referential integrity error
        'ds.Tables("Authors").Rows(0).Delete()
        'ds.Tables("Authors").Rows(1).Delete()
        'ds.Tables("Authors").Rows(2).Delete()

        ' Change some values.
        With ds.Tables("Authors")
            .Rows(0)("au_fname") = .Rows(0)("au_fname").ToString & " New"
            .Rows(1)("au_fname") = .Rows(1)("au_fname").ToString & " New"
        End With

        ' Send changes them to the database.
        ' Again, no need to explicitly open and close the connection.
        da.Update(ds, "Authors")

    End Sub

End Class